/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAL;

import DTO.DTOOrder;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

/**
 *
 * @author Pham
 */
public class DALOrder {
    ConnectionDB conn;
    
    public DALOrder() 
    {
        conn = new ConnectionDB();
    }
    
    //method get all orders
    public ArrayList<DTOOrder> getOrders()
    {
        try{
            ArrayList<DTOOrder> listOrder = new ArrayList<>();
            String querySelect = "SELECT * FROM  `order` ORDER BY `order_datecreated` DESC";
            ResultSet rs = conn.executeQuery(querySelect);
            while(rs.next())
            {
                listOrder.add(new DTOOrder(rs));
            }
            return listOrder;
            
        }
        catch(Exception ex)
        {
            System.err.printf(ex.getMessage());
            return null;
        }
    }
    
    //method get order by id
    public DTOOrder getOrderbyId(String Ordid)
    {
        try
        {
            DTOOrder dtoOrder = null;
            String querySelect = "select * from `order` where `order_id` = '"+ Ordid +"'";
            ResultSet rs = conn.executeQuery(querySelect);
            while(rs.next())
            {
                dtoOrder = new DTOOrder(rs);
            }
            return dtoOrder;
        }
        catch(Exception ex)
        {
            System.err.printf(ex.getMessage());
            return null;
        }
    }
    
    //method filter order
    public ArrayList<DTOOrder> filterOrder(Date dateFrom, Date dateTo, int state)
    {
        try{
            String querySelect = "select * from `order` where 1";
            ArrayList<DTOOrder> listOrder = new ArrayList<>();
            if(state > -1)
                querySelect += " and order_state = " + state;
            if(dateFrom != null && dateTo != null){
                DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
                String dateFromString = df.format(dateFrom);
                DateFormat df2 = new SimpleDateFormat("yyyy-MM-dd 23:59:59");
                String dateToString = df2.format(dateTo);
                querySelect += " and order_datecreated between '" + dateFromString + "' AND '" + dateToString + "'";
            }
                
            ResultSet rs = conn.executeQuery(querySelect);
            while(rs.next()){
                listOrder.add(new DTOOrder(rs));
            }
            return listOrder;
        }
        catch(Exception ex){
            System.err.printf(ex.getMessage());
            return null;
        }
    }
    
    //method update state order
    public boolean updateState(DTOOrder dtoOrd)
    {
        try{
            DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String date = df.format(dtoOrd.getDateExport());
            String queryUpdate = "update `order` set order_state = " + dtoOrd.getOrderState() + ", "
                                + "date_export = '" + date + "' where order_id = '"+dtoOrd.getOrderId()+"'";
            if(conn.exeUpdate(queryUpdate))
                return true;
            return false;
        }
        catch(Exception ex)
        {
            System.err.printf(ex.getMessage());
            return false;
        }
    }
    
    //method create new order
    public boolean createOder(DTOOrder dtoOrd)
    {
        try{
            SimpleDateFormat formatDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String dateCreated = formatDate.format(dtoOrd.getOrderDateCreated());
            String queryInsert = "insert into `order`(`order_id`, `order_datecreated`, `order_guessname_delivery`, `order_guessaddress_delivery`, "
                    + "`order_guessphone_delivery`, `order_guessemail_delivery`, `order_state`) "
                    + "values(?, ?, ?, ?, ?, ?, ?)";
            PreparedStatement pstmt = conn.getConnectionDB().prepareStatement(queryInsert);
            pstmt.setString(1, dtoOrd.getOrderId());
            pstmt.setString(2, dateCreated);
            pstmt.setString(3, dtoOrd.getOrderGuessName());
            pstmt.setString(4, dtoOrd.getOrderGuessAddress());
            pstmt.setString(5, dtoOrd.getOrderGuessPhone());
            pstmt.setString(6, dtoOrd.getOrderGuessEmail());
            pstmt.setInt(7, dtoOrd.getOrderState());
            if(pstmt.executeUpdate() == 1 )
                return true;
        }
        catch(Exception ex)
        {
            System.err.print(ex);
        }
        return false;
    }
    
    //method delete order
    public boolean deleteOrder(String orderId)
    {
        try{
            String queryDel = "DELETE FROM `order` WHERE `order_id` = '" + orderId + "'";
            return conn.exeUpdate(queryDel);
        }
        catch(Exception ex)
        {
            System.err.println(ex);
            return false;
        }
    }
}
